{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f0936a78",
   "metadata": {},
   "source": [
    "# 数据表处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "858a546a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "487eb07d",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = r'C:\\Users\\illus\\OneDrive\\Git\\Data-Analysis\\lecture-python\\raw-data'  # 前面加r, 防止转义\n",
    "os.chdir(data_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a5a6fd0",
   "metadata": {},
   "source": [
    "## 数值查看和获取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "0de4be11",
   "metadata": {
    "hide_input": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 12 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   PassengerId  891 non-null    object \n",
      " 1   Survived     891 non-null    int64  \n",
      " 2   Pclass       891 non-null    int64  \n",
      " 3   Name         891 non-null    object \n",
      " 4   Sex          891 non-null    object \n",
      " 5   Age          714 non-null    float64\n",
      " 6   SibSp        891 non-null    int64  \n",
      " 7   Parch        891 non-null    int64  \n",
      " 8   Ticket       891 non-null    object \n",
      " 9   Fare         891 non-null    float64\n",
      " 10  Cabin        204 non-null    object \n",
      " 11  Embarked     889 non-null    object \n",
      "dtypes: float64(2), int64(4), object(6)\n",
      "memory usage: 83.7+ KB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "PassengerId    False\n",
       "Survived       False\n",
       "Pclass         False\n",
       "Name           False\n",
       "Sex            False\n",
       "Age             True\n",
       "SibSp          False\n",
       "Parch          False\n",
       "Ticket         False\n",
       "Fare            True\n",
       "Cabin          False\n",
       "Embarked       False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 导入数据\n",
    "\n",
    "df = pd.read_excel('titanic.xlsx',\n",
    "                   sheet_name='titanic',\n",
    "                   dtype={'PassengerId': str})\n",
    "\n",
    "# # 查看数据\n",
    "# df.head()\n",
    "# print(df.columns)\n",
    "# # 确认变量含义，如果变量很多，确认需要使用的变量\n",
    "\n",
    "print(df.info())\n",
    "# print(df.describe()) \n",
    "\n",
    "# print(df.describe().columns)  # quick way to separate numeric columns\n",
    "# # or by\n",
    "# print(df.dtypes[df.dtypes == 'int64'].index)  # 提取int\n",
    "\n",
    "# print(df.dtypes[df.dtypes.isin(['int64', 'float64'])])\n",
    "df.dtypes.isin(['float64','int64'])  # float64不行，why?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "id": "eb92fbaf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId  Survived     Sex\n",
       "0           1         0    male\n",
       "1           2         1  female\n",
       "2           3         1  female\n",
       "3           4         1  female\n",
       "4           5         0    male\n",
       "5           6         0    male\n",
       "6           7         0    male\n",
       "7           8         0    male\n",
       "8           9         1  female\n",
       "9          10         1  female"
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取数值\n",
    "'''\n",
    "基础索引：df[]\n",
    "iloc: 使用位置\n",
    "loc: 使用索引\n",
    "'''\n",
    "\n",
    "df[['PassengerId','Survived','Sex']][0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbc9a3eb",
   "metadata": {},
   "source": [
    "## 增、删、改、查找"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "267af230",
   "metadata": {},
   "source": [
    "### 增"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4192796",
   "metadata": {},
   "source": [
    "#### 增加列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "2ba38001",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 增加数据\n",
    "df2 = df.copy() # 复制一份数据\n",
    "df2 = df.copy(deep = True) # 复制一份数据\n",
    "\n",
    "# 增加1列\n",
    "df2['new_variable'] = list(range(891))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "85cc233e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[5, 2]</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[2, 4]</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[1, 2, 3]</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           a   b\n",
       "0     [5, 2]  10\n",
       "1     [2, 4]  11\n",
       "2  [1, 2, 3]  10"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# why deep = True, 如果dataframe中含有列表，使用浅copy对列表内容的修改也会影响原dataframe\n",
    "\n",
    "dict = {'a':[[1,2],[2,4],[1,2,3]],'b':[10,11,10]}\n",
    "a = pd.DataFrame(dict)\n",
    "b = a.copy()\n",
    "b.iloc[0,0][0] = 5\n",
    "a\n",
    "b.iloc[0,1] = 50\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "id": "f99fbc44",
   "metadata": {
    "hide_input": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "      <th>new_variable</th>\n",
       "      <th>sex_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Braund, Mr. Owen Harris</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5 21171</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C85</td>\n",
       "      <td>C</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Heikkinen, Miss. Laina</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101282</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId  Survived  Pclass  \\\n",
       "0           1         0       3   \n",
       "1           2         1       1   \n",
       "2           3         1       3   \n",
       "\n",
       "                                                Name     Sex   Age  SibSp  \\\n",
       "0                            Braund, Mr. Owen Harris    male  22.0      1   \n",
       "1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "2                             Heikkinen, Miss. Laina  female  26.0      0   \n",
       "\n",
       "   Parch            Ticket     Fare Cabin Embarked  new_variable  sex_num  \n",
       "0      0         A/5 21171   7.2500   NaN        S             0        0  \n",
       "1      0          PC 17599  71.2833   C85        C             1        1  \n",
       "2      0  STON/O2. 3101282   7.9250   NaN        S             2        1  "
      ]
     },
     "execution_count": 279,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['sex_num'] = np.where(df2['Sex'] == 'male', 0, 1)\n",
    "df2.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "id": "1a3985c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "      <th>new_variable</th>\n",
       "      <th>sex_num</th>\n",
       "      <th>sex_num2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Braund, Mr. Owen Harris</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5 21171</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C85</td>\n",
       "      <td>C</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Heikkinen, Miss. Laina</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101282</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId  Survived  Pclass  \\\n",
       "0           1         0       3   \n",
       "1           2         1       1   \n",
       "2           3         1       3   \n",
       "\n",
       "                                                Name     Sex   Age  SibSp  \\\n",
       "0                            Braund, Mr. Owen Harris    male  22.0      1   \n",
       "1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "2                             Heikkinen, Miss. Laina  female  26.0      0   \n",
       "\n",
       "   Parch            Ticket     Fare Cabin Embarked  new_variable  sex_num  \\\n",
       "0      0         A/5 21171   7.2500   NaN        S             0        0   \n",
       "1      0          PC 17599  71.2833   C85        C             1        1   \n",
       "2      0  STON/O2. 3101282   7.9250   NaN        S             2        1   \n",
       "\n",
       "   sex_num2  \n",
       "0         0  \n",
       "1         1  \n",
       "2         1  "
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['sex_num2'] = df2['Sex'].map(lambda x: 0 if x == 'male' else 1)\n",
    "df2.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b001c042",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 指定位置添加列；注意：列名(不能与存在列名相同)\n",
    "\n",
    "df2.insert(2,'new_variable',range(891))  # 位置，新的变量名，变量\n",
    "#df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f12dee66",
   "metadata": {},
   "source": [
    "#### 增加行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "id": "b0a8828c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>new_variable3</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>891</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>Nasser, Mrs. Nicholas (Adele Achem)</td>\n",
       "      <td>female</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>237736</td>\n",
       "      <td>30.0708</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>892</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>Sandstrom, Miss. Marguerite Rut</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>PP 9549</td>\n",
       "      <td>16.7000</td>\n",
       "      <td>G6</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>893</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>Bonnell, Miss. Elizabeth</td>\n",
       "      <td>female</td>\n",
       "      <td>58.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113783</td>\n",
       "      <td>26.5500</td>\n",
       "      <td>C103</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    PassengerId  Survived  new_variable3  Pclass  \\\n",
       "891          10         1              9       2   \n",
       "892          11         1             10       3   \n",
       "893          12         1             11       1   \n",
       "\n",
       "                                    Name     Sex   Age  SibSp  Parch   Ticket  \\\n",
       "891  Nasser, Mrs. Nicholas (Adele Achem)  female  14.0      1      0   237736   \n",
       "892      Sandstrom, Miss. Marguerite Rut  female   4.0      1      1  PP 9549   \n",
       "893             Bonnell, Miss. Elizabeth  female  58.0      0      0   113783   \n",
       "\n",
       "        Fare Cabin Embarked  \n",
       "891  30.0708   NaN        C  \n",
       "892  16.7000    G6        S  \n",
       "893  26.5500  C103        S  "
      ]
     },
     "execution_count": 316,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use append\n",
    "new = df2.iloc[9:12,:]\n",
    "df3 = df2.append(new,ignore_index=True)\n",
    "# True 按照新的数组的行索引排列\n",
    "# False 保持原有的 \n",
    "df2.shape, df3.shape\n",
    "df3.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "id": "e95c2412",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame(columns = df2.columns)\n",
    "df3 = df3.append(new,ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b91984b8",
   "metadata": {},
   "source": [
    "### 删"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b21e1ccf",
   "metadata": {},
   "source": [
    "#### 删除列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "id": "cc817677",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PassengerId', 'Survived', 'new_variable3', 'new_variable2', 'Pclass',\n",
       "       'Name', 'Sex', 'Age', 'SibSp', 'Parch', 'Ticket', 'Fare', 'Cabin',\n",
       "       'Embarked', 'new_variable', 'sex_num', 'sex_num2'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 299,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "id": "78c87f47",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PassengerId', 'Survived', 'new_variable3', 'Pclass', 'Name', 'Sex',\n",
       "       'Age', 'SibSp', 'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 310,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.drop(labels = ['new_variable','new_variable2','sex_num','sex_num2'], axis = 1, inplace = True)\n",
    "df3.columns\n",
    "\n",
    "# 同时删除多个变量，需要以列表的形式\n",
    "# inplace =True,代表是否对原数据操作, 否则返回的是视图，并没有对原数据进行操作\n",
    "# axis = 1代表删除列； axis = 0 表示删除行；默认 axis = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ffa5732a",
   "metadata": {},
   "source": [
    "#### 删除行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "id": "cf5d1046",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>new_variable3</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>Sandstrom, Miss. Marguerite Rut</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>PP 9549</td>\n",
       "      <td>16.70</td>\n",
       "      <td>G6</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>Bonnell, Miss. Elizabeth</td>\n",
       "      <td>female</td>\n",
       "      <td>58.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113783</td>\n",
       "      <td>26.55</td>\n",
       "      <td>C103</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId Survived new_variable3 Pclass                             Name  \\\n",
       "1          11        1            10      3  Sandstrom, Miss. Marguerite Rut   \n",
       "2          12        1            11      1         Bonnell, Miss. Elizabeth   \n",
       "\n",
       "      Sex   Age SibSp Parch   Ticket   Fare Cabin Embarked  \n",
       "1  female   4.0     1     1  PP 9549  16.70    G6        S  \n",
       "2  female  58.0     0     0   113783  26.55  C103        S  "
      ]
     },
     "execution_count": 320,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.drop(labels = 0,axis = 0, inplace = True)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "324cb1b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.drop(columns = ['Survived','Pclass'] )\n",
    "df2.drop(index = [0,1,2])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f27a132c",
   "metadata": {},
   "source": [
    "### 修改"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b54b0e8",
   "metadata": {},
   "source": [
    "#### 修改列名、行索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5fec6481",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.columns = new_col\n",
    "# df.index = new_index\n",
    "\n",
    "# df.rename(index = {}, columns = {}, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa2c254d",
   "metadata": {},
   "source": [
    "#### 修改数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5b1f06a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df['col'] = [] #修改列\n",
    "# df[0] = []  # 修改行\n",
    "\n",
    "# 将Sex为female的改为女性，male改为男性\n",
    "df2.loc[df2['Sex'] == 'female', 'sex2'] = '女性'\n",
    "df2.loc[df2['Sex'] == 'male', 'sex2'] = '男性'\n",
    "df2.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "id": "5465ca6a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['male', 'female'], dtype=object)"
      ]
     },
     "execution_count": 325,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check unique values of Sex in case\n",
    "df2.Sex.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70e597fb",
   "metadata": {},
   "source": [
    "### 查找"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb91bf8c",
   "metadata": {},
   "source": [
    "#### 条件查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8c94880",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查看女性数据\n",
    "df2[df2['Sex'] == 'female']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9660cb33",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df2[df2['Sex'] == 'female']['Survived']\n",
    "df2.loc[df2['Sex'] == 'female','Survived']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "id": "1c57a19d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>14.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>15.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>14.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>855</th>\n",
       "      <td>18.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>858</th>\n",
       "      <td>24.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>869</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>875</th>\n",
       "      <td>15.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>19.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>123 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Age  Survived     Sex\n",
       "9    14.0         1  female\n",
       "10    4.0         1  female\n",
       "22   15.0         1  female\n",
       "39   14.0         1  female\n",
       "43    3.0         1  female\n",
       "..    ...       ...     ...\n",
       "855  18.0         1  female\n",
       "858  24.0         1  female\n",
       "869   4.0         1    male\n",
       "875  15.0         1  female\n",
       "887  19.0         1  female\n",
       "\n",
       "[123 rows x 3 columns]"
      ]
     },
     "execution_count": 335,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看 Age < 25 和 Age > 60 的数据\n",
    "df2.loc[((df2['Age']<25) | (df2['Age']>60))& (df2['Survived'] == 1) ,['Age','Survived','Sex']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3e4a55b",
   "metadata": {},
   "source": [
    "#### 使用between() 和 isin()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "id": "fb5a20fe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(17, 14)"
      ]
     },
     "execution_count": 342,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# between()\n",
    "df2[ df2['Age'].between(4,6)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a6659cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd.isin()\n",
    "# 包含\n",
    "df2[df2['Age'].isin([4,28,30,60])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b85701ff",
   "metadata": {},
   "source": [
    "#### 字符串向量化操作 str\n",
    "\n",
    "- 见后面内容"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "758dbb41",
   "metadata": {},
   "source": [
    "## 数据合并\n",
    "\n",
    "- 有join, merge 和 concat 三种方法，用于合并，支持 左链接，右链接，内链接，外链接\n",
    "\n",
    "    - 左链接（left）：使用左边df的行索引\n",
    "    - 右链接（right）：使用右边df的行索引\n",
    "    - 内链接（inner）：取行索引的交集\n",
    "    - 外链接（outer）：取行索引的并集"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1bf8d61",
   "metadata": {},
   "source": [
    "### Join\n",
    "\n",
    "- `df.join(df2, on = key, how = 'left')`\n",
    "- 将一个或多个DataFrame加入到当前DataFrame中，实现合并的功能\n",
    "- 默认以**左连接**的方式进行合并，默认的**连接列是DataFrame的行索引**，并且，合并两个DataFrame时，两个DataFrame中不能有相同的列名\n",
    "  - 如果相同的列名，需要传入`rsuffix` 和`lsuffix` 参数\n",
    "- on：指定连接列\n",
    "  - NOTE: \n",
    "    - 只能指定调用join()方法的DataFrame，而传入join()方法的DataFrame还是用行索引进行连接\n",
    "    - on参数指定多个列作为连接列时，这些列都要在调用join()方法的DataFrame中。并且传入join()方法的DataFrame必须为多重行索引(MultiIndex)，且与on指定的列数相等。\n",
    "    - 如果第一个DataFrame是单行索引，第二个DataFrame是多重行索引，且未指定on参数，那么，我们需要给两个DataFrame的行索引命名，并且单行索引的索引名要包含在多重行索引的索引名中。\n",
    "- 可以做多表合并 `df.join([df2,df3])\n",
    "  - 多表合并只支持用行索引进行连接，不能使用on参数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a7468a29",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Red  Green  key\n",
      "a    1      5    1\n",
      "b    3      0    3\n",
      "c    5      3    2\n",
      "d    6      2    4\n",
      "   Blue  Yellow  key\n",
      "c     1       6    3\n",
      "d     9       6    2\n",
      "e     8       7    4\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "      <th>Blue</th>\n",
       "      <th>Yellow</th>\n",
       "      <th>key</th>\n",
       "      <th>Brown</th>\n",
       "      <th>White</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Red  Green  Blue  Yellow  key  Brown  White\n",
       "a  1.0    5.0   NaN     NaN  NaN    3.0    1.0\n",
       "b  3.0    0.0   NaN     NaN  NaN    NaN    NaN\n",
       "c  5.0    3.0   1.0     6.0  3.0    NaN    NaN\n",
       "d  6.0    2.0   9.0     6.0  2.0    5.0    2.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3=pd.DataFrame({'Red':[1,3,5,6],'Green':[5,0,3,2],'key':[1,3,2,4]},index=list('abcd'))\n",
    "df4=pd.DataFrame({'Blue':[1,9,8],'Yellow':[6,6,7],'key':[3,2,4]},index=list('cde'))\n",
    "print(df3)\n",
    "print(df4)\n",
    "\n",
    "# 默认左连接\n",
    "df3.join(df4, rsuffix = '_right', lsuffix = '_left')  #,how = 'left'\n",
    "\n",
    "# 右连接\n",
    "df3.join(df4,rsuffix = '_right', lsuffix = '_left',how='right')\n",
    "\n",
    "# 内连接\n",
    "df3.join(df4,how='inner', rsuffix = '_right', lsuffix = '_left')\n",
    "\n",
    "# 外连接\n",
    "df3.join(df4,how = 'outer',rsuffix = '_right', lsuffix = '_left')\n",
    "\n",
    "# 多表合并， 只能基于行索引合并\n",
    "df6 = df3.drop(columns = ['key'])\n",
    "df5=pd.DataFrame({'Brown':[3,4,5],'White':[1,1,2]},index=list('aed'))\n",
    "df6.join([df4,df5])  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "907f15de",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Red  Green  key\n",
      "a    1      5    1\n",
      "b    3      0    3\n",
      "c    5      3    2\n",
      "d    6      2    4\n",
      "   Blue  Yellow  key\n",
      "c     1       6    3\n",
      "d     9       6    2\n",
      "e     8       7    4\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "      <th>key</th>\n",
       "      <th>Blue</th>\n",
       "      <th>Yellow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Red  Green  key  Blue  Yellow\n",
       "a    1      5    1   NaN     NaN\n",
       "b    3      0    3   1.0     6.0\n",
       "c    5      3    2   9.0     6.0\n",
       "d    6      2    4   8.0     7.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 基于列合并\n",
    "df3=pd.DataFrame({'Red':[1,3,5,6],'Green':[5,0,3,2],'key':[1,3,2,4]},index=list('abcd'))\n",
    "df4=pd.DataFrame({'Blue':[1,9,8],'Yellow':[6,6,7],'key':[3,2,4]},index=list('cde'))\n",
    "print(df3)\n",
    "print(df4)\n",
    "\n",
    "# 基于'key' 合并\n",
    "\n",
    "# 方法1: 转换为索引\n",
    "df3.set_index('key').join(df4.set_index('key'))\n",
    "# 方法2: 使用 on\n",
    "df3.join(df4.set_index('key'), on = 'key') "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d345b54",
   "metadata": {},
   "source": [
    "### Merge\n",
    "\n",
    "- 使用merge，着重关注的是列的合并\n",
    "- 合并使用关联字段必须类型一致"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "edcf7d44",
   "metadata": {},
   "outputs": [],
   "source": [
    "baby_trade = pd.read_csv('baby_trade_history.csv', encoding='utf-8',dtype={'user_id':str})# 交易数据\n",
    "baby_info = pd.read_csv('sam_tianchi_mum_baby.csv',encoding = 'utf-8',dtype =str)#婴儿信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 354,
   "id": "f2e7f4f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>cat1</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>786295544</td>\n",
       "      <td>41098319944</td>\n",
       "      <td>50014866</td>\n",
       "      <td>50022520</td>\n",
       "      <td>21458:86755362;13023209:3593274;10984217:21985...</td>\n",
       "      <td>2</td>\n",
       "      <td>20140919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>532110457</td>\n",
       "      <td>17916191097</td>\n",
       "      <td>50011993</td>\n",
       "      <td>28</td>\n",
       "      <td>21458:11399317;1628862:3251296;21475:137325;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>249013725</td>\n",
       "      <td>21896936223</td>\n",
       "      <td>50012461</td>\n",
       "      <td>50014815</td>\n",
       "      <td>21458:30992;1628665:92012;1628665:3233938;1628...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>917056007</td>\n",
       "      <td>12515996043</td>\n",
       "      <td>50018831</td>\n",
       "      <td>50014815</td>\n",
       "      <td>21458:15841995;21956:3494076;27000458:59723383...</td>\n",
       "      <td>2</td>\n",
       "      <td>20141023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>444069173</td>\n",
       "      <td>20487688075</td>\n",
       "      <td>50013636</td>\n",
       "      <td>50008168</td>\n",
       "      <td>21458:30992;13658074:3323064;1628665:3233941;1...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_id   auction_id    cat_id      cat1  \\\n",
       "0  786295544  41098319944  50014866  50022520   \n",
       "1  532110457  17916191097  50011993        28   \n",
       "2  249013725  21896936223  50012461  50014815   \n",
       "3  917056007  12515996043  50018831  50014815   \n",
       "4  444069173  20487688075  50013636  50008168   \n",
       "\n",
       "                                            property  buy_mount       day  \n",
       "0  21458:86755362;13023209:3593274;10984217:21985...          2  20140919  \n",
       "1  21458:11399317;1628862:3251296;21475:137325;16...          1  20131011  \n",
       "2  21458:30992;1628665:92012;1628665:3233938;1628...          1  20131011  \n",
       "3  21458:15841995;21956:3494076;27000458:59723383...          2  20141023  \n",
       "4  21458:30992;13658074:3323064;1628665:3233941;1...          1  20141103  "
      ]
     },
     "execution_count": 354,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_trade.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 355,
   "id": "4bfa213a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>birthday</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2757</td>\n",
       "      <td>20130311</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>415971</td>\n",
       "      <td>20121111</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1372572</td>\n",
       "      <td>20120130</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10339332</td>\n",
       "      <td>20110910</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10642245</td>\n",
       "      <td>20130213</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id  birthday gender\n",
       "0      2757  20130311      1\n",
       "1    415971  20121111      0\n",
       "2   1372572  20120130      1\n",
       "3  10339332  20110910      0\n",
       "4  10642245  20130213      0"
      ]
     },
     "execution_count": 355,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "840d8c7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>birthday</th>\n",
       "      <th>gender</th>\n",
       "      <th>day_left</th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>cat1</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day_right</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2757</td>\n",
       "      <td>20130311</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17429550751</td>\n",
       "      <td>50010555</td>\n",
       "      <td>50008168</td>\n",
       "      <td>21458:30992;25935:31381;1628665:29796;1628665:...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>415971</td>\n",
       "      <td>20121111</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20854308837</td>\n",
       "      <td>50010548</td>\n",
       "      <td>50008168</td>\n",
       "      <td>1628665:131622;25935:21991;22019:31001;22019:3...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1372572</td>\n",
       "      <td>20120130</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>16915013171</td>\n",
       "      <td>50008845</td>\n",
       "      <td>28</td>\n",
       "      <td>21458:30992;1628665:3233941;1628665:3233942;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10339332</td>\n",
       "      <td>20110910</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13174075495</td>\n",
       "      <td>50001732</td>\n",
       "      <td>50014815</td>\n",
       "      <td>21458:3409452;3066697:92335415;2815901:9233541...</td>\n",
       "      <td>1</td>\n",
       "      <td>20140526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10642245</td>\n",
       "      <td>20130213</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14109039851</td>\n",
       "      <td>50006843</td>\n",
       "      <td>38</td>\n",
       "      <td>21458:7142737;8694098:95303334;12786373:54223;...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130617</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id  birthday gender  day_left   auction_id    cat_id      cat1  \\\n",
       "0      2757  20130311      1       0.0  17429550751  50010555  50008168   \n",
       "1    415971  20121111      0       1.0  20854308837  50010548  50008168   \n",
       "2   1372572  20120130      1       2.0  16915013171  50008845        28   \n",
       "3  10339332  20110910      0       3.0  13174075495  50001732  50014815   \n",
       "4  10642245  20130213      0       4.0  14109039851  50006843        38   \n",
       "\n",
       "                                            property  buy_mount  day_right  \n",
       "0  21458:30992;25935:31381;1628665:29796;1628665:...          1   20130410  \n",
       "1  1628665:131622;25935:21991;22019:31001;22019:3...          1   20130128  \n",
       "2  21458:30992;1628665:3233941;1628665:3233942;16...          1   20130327  \n",
       "3  21458:3409452;3066697:92335415;2815901:9233541...          1   20140526  \n",
       "4  21458:7142737;8694098:95303334;12786373:54223;...          1   20130617  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pd.merge()  or  df.merge(df2)\n",
    "baby_all = pd.merge(baby_info, baby_trade,on='user_id', how = 'outer')  \n",
    "baby_all.head()\n",
    "# how = 'right', 'left','outer', 默认是 'inner'\n",
    "# 如果两个表合并依据的变量名不一致，使用  right_on ='user_id', left_on = 'user_id'\n",
    "# 如果使用索引， right_index = True,  left_index = True\n",
    "# 若有相同列且该列没有作为合并的列，可通过suffixes设置该列的后缀名，一般为元组和列表类型： suffixes = ('_left', '_right') \n",
    "# 如果没有指定suffixes，merge方法会自动添加后缀\n",
    "baby_info['day'] = range(baby_info.shape[0])\n",
    "baby_all = pd.merge(baby_info, baby_trade,on='user_id', how = 'outer',suffixes=('_left','_right'))  \n",
    "baby_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa28a0f9",
   "metadata": {},
   "source": [
    "###  concat\n",
    "\n",
    "- 轴向连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "25abde5d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a    1\n",
      "b    2\n",
      "dtype: int64\n",
      "b    3\n",
      "d    4\n",
      "e    5\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B\n",
       "a  1.0  NaN\n",
       "b  2.0  3.0\n",
       "d  NaN  4.0\n",
       "e  NaN  5.0"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Series对象的连接\n",
    "s1=pd.Series([1,2],index=list('ab'))\n",
    "s2=pd.Series([3,4,5],index=list('bde'))\n",
    "print(s1)\n",
    "print(s2)\n",
    "pd.concat([s1,s2])  # 默认纵向堆叠， axis = 0\n",
    "\n",
    "pd.concat([s1,s2],axis=1)  # 横向堆叠, 默认为 outer\n",
    "\n",
    "pd.concat([s1,s2],axis=1,join='inner')\n",
    "\n",
    "pd.concat([s1,s2],axis=1,join='outer',keys=['A','B']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "96f22dcc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Red  Green  Yellow\n",
      "a    1      5       6\n",
      "b    3      0       6\n",
      "d    5      3       7\n",
      "   Blue  Yellow\n",
      "c     1       6\n",
      "e     9       6\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">A</th>\n",
       "      <th colspan=\"2\" halign=\"left\">B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "      <th>Yellow</th>\n",
       "      <th>Blue</th>\n",
       "      <th>Yellow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A                 B       \n",
       "   Red Green Yellow Blue Yellow\n",
       "a  1.0   5.0    6.0  NaN    NaN\n",
       "b  3.0   0.0    6.0  NaN    NaN\n",
       "d  5.0   3.0    7.0  NaN    NaN\n",
       "c  NaN   NaN    NaN  1.0    6.0\n",
       "e  NaN   NaN    NaN  9.0    6.0"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dataframe对象的连接\n",
    "\n",
    "df3=pd.DataFrame({'Red':[1,3,5],'Green':[5,0,3],'Yellow':[6,6,7]},index=list('abd'))\n",
    "df4=pd.DataFrame({'Blue':[1,9],'Yellow':[6,6]},index=list('ce'))\n",
    "print(df3)\n",
    "print(df4) \n",
    "\n",
    "# pd.concat([df3,df4]) \n",
    "\n",
    "pd.concat([df3,df4],axis=1)\n",
    "\n",
    "pd.concat({'A':df3,'B':df4},axis=1)\n",
    "# pd.concat([df3,df4],axis = 1, keys = ['A','B'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1446e2c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import xlrd\n",
    "# workbook = xlrd.open_workbook('meal_order_detail.xlsx')\n",
    "# sheet_name = workbook.sheet_names() #返回所有sheet的列表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "47eefd41",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3647, 19)"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 练习: 合并 meal_order_detail.xlsx 三个时期的数据，纵向堆叠，meal_order_detail 有三个表\n",
    "# 查看一下数据\n",
    "data1 = pd.read_excel('meal_order_detail.xlsx',sheet_name=0)\n",
    "data2 = pd.read_excel('meal_order_detail.xlsx',sheet_name=1)\n",
    "data2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "bdf707d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {}\n",
    "for i in range(3):\n",
    "    data['order'+str(i+1)] = pd.read_excel('meal_order_detail.xlsx',sheet_name = i)\n",
    "data_all = pd.concat(data.values())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "94bf89a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# data = []\n",
    "# for i in range(3):    \n",
    "#     data.append(pd.read_excel('meal_order_detail.xlsx',sheet_name = i))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 413,
   "id": "f64bc4c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_all = pd.DataFrame()\n",
    "for i in range(3):\n",
    "    tem = pd.read_excel('meal_order_detail.xlsx',sheet_name = i)\n",
    "    data_all = pd.concat([data_all,tem])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89ac539e",
   "metadata": {},
   "source": [
    "## 层次化索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 431,
   "id": "9cdd2079",
   "metadata": {},
   "outputs": [],
   "source": [
    "baby_trade = pd.read_csv('baby_trade_history.csv', dtype={'user_id':str},index_col=[3,0]) #将数据第4列和第1列当成索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 432,
   "id": "e95af9d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat1</th>\n",
       "      <th>user_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50022520</th>\n",
       "      <th>786295544</th>\n",
       "      <td>41098319944</td>\n",
       "      <td>50014866</td>\n",
       "      <td>21458:86755362;13023209:3593274;10984217:21985...</td>\n",
       "      <td>2</td>\n",
       "      <td>20140919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <th>532110457</th>\n",
       "      <td>17916191097</td>\n",
       "      <td>50011993</td>\n",
       "      <td>21458:11399317;1628862:3251296;21475:137325;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">50014815</th>\n",
       "      <th>249013725</th>\n",
       "      <td>21896936223</td>\n",
       "      <td>50012461</td>\n",
       "      <td>21458:30992;1628665:92012;1628665:3233938;1628...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917056007</th>\n",
       "      <td>12515996043</td>\n",
       "      <td>50018831</td>\n",
       "      <td>21458:15841995;21956:3494076;27000458:59723383...</td>\n",
       "      <td>2</td>\n",
       "      <td>20141023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50008168</th>\n",
       "      <th>444069173</th>\n",
       "      <td>20487688075</td>\n",
       "      <td>50013636</td>\n",
       "      <td>21458:30992;13658074:3323064;1628665:3233941;1...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     auction_id    cat_id  \\\n",
       "cat1     user_id                            \n",
       "50022520 786295544  41098319944  50014866   \n",
       "28       532110457  17916191097  50011993   \n",
       "50014815 249013725  21896936223  50012461   \n",
       "         917056007  12515996043  50018831   \n",
       "50008168 444069173  20487688075  50013636   \n",
       "\n",
       "                                                             property  \\\n",
       "cat1     user_id                                                        \n",
       "50022520 786295544  21458:86755362;13023209:3593274;10984217:21985...   \n",
       "28       532110457  21458:11399317;1628862:3251296;21475:137325;16...   \n",
       "50014815 249013725  21458:30992;1628665:92012;1628665:3233938;1628...   \n",
       "         917056007  21458:15841995;21956:3494076;27000458:59723383...   \n",
       "50008168 444069173  21458:30992;13658074:3323064;1628665:3233941;1...   \n",
       "\n",
       "                    buy_mount       day  \n",
       "cat1     user_id                         \n",
       "50022520 786295544          2  20140919  \n",
       "28       532110457          1  20131011  \n",
       "50014815 249013725          1  20131011  \n",
       "         917056007          2  20141023  \n",
       "50008168 444069173          1  20141103  "
      ]
     },
     "execution_count": 432,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_trade.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 481,
   "id": "3558d02a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat1</th>\n",
       "      <th>user_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <th>532110457</th>\n",
       "      <td>17916191097</td>\n",
       "      <td>50011993</td>\n",
       "      <td>21458:11399317;1628862:3251296;21475:137325;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 auction_id    cat_id  \\\n",
       "cat1 user_id                            \n",
       "28   532110457  17916191097  50011993   \n",
       "\n",
       "                                                         property  buy_mount  \\\n",
       "cat1 user_id                                                                   \n",
       "28   532110457  21458:11399317;1628862:3251296;21475:137325;16...          1   \n",
       "\n",
       "                     day  \n",
       "cat1 user_id              \n",
       "28   532110457  20131011  "
      ]
     },
     "execution_count": 481,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_trade.loc[28].head() #第一层引用\n",
    "baby_trade.loc[28][0:2]  #第二层引用\n",
    "# 直接引用两层\n",
    "baby_trade.loc[28,532110457,:]\n",
    "#baby_trade.loc[(28,532110457),'cat_id'] # 使用tuple\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 465,
   "id": "66094f65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat1</th>\n",
       "      <th>user_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">28</th>\n",
       "      <th>532110457</th>\n",
       "      <td>17916191097</td>\n",
       "      <td>50011993</td>\n",
       "      <td>21458:11399317;1628862:3251296;21475:137325;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82830661</th>\n",
       "      <td>19948600790</td>\n",
       "      <td>50013874</td>\n",
       "      <td>21458:11580;21475:137325</td>\n",
       "      <td>1</td>\n",
       "      <td>20121101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475046636</th>\n",
       "      <td>10368360710</td>\n",
       "      <td>203527</td>\n",
       "      <td>22724:40168;22729:40278;21458:21817;2770200:24...</td>\n",
       "      <td>1</td>\n",
       "      <td>20121101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377550424</th>\n",
       "      <td>15771663914</td>\n",
       "      <td>50015841</td>\n",
       "      <td>1628665:3233941;1628665:3233942;3914866:11580;...</td>\n",
       "      <td>1</td>\n",
       "      <td>20121123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>530850018</th>\n",
       "      <td>22058239899</td>\n",
       "      <td>50024147</td>\n",
       "      <td>21458:205007542;43307470:5543413;2339128:62147...</td>\n",
       "      <td>1</td>\n",
       "      <td>20140210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">50014815</th>\n",
       "      <th>413188001</th>\n",
       "      <td>16521677358</td>\n",
       "      <td>50012478</td>\n",
       "      <td>21458:28155;5434803:3636603;2815901:22583732;1...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641734831</th>\n",
       "      <td>22105131076</td>\n",
       "      <td>50014277</td>\n",
       "      <td>21458:21906;13227811:51479;13230966:75369014;3...</td>\n",
       "      <td>2</td>\n",
       "      <td>20141016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268356658</th>\n",
       "      <td>36932456353</td>\n",
       "      <td>50010236</td>\n",
       "      <td>21458:10513072;12474507:706291650;3091143:9208...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196272909</th>\n",
       "      <td>10066997901</td>\n",
       "      <td>50009540</td>\n",
       "      <td>21458:21906;13229910:32056435;2191928:73664723...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23473499</th>\n",
       "      <td>38019470815</td>\n",
       "      <td>50010236</td>\n",
       "      <td>1628665:61550;1628665:3233940;1628665:3233936;...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141104</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11797 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     auction_id    cat_id  \\\n",
       "cat1     user_id                            \n",
       "28       532110457  17916191097  50011993   \n",
       "         82830661   19948600790  50013874   \n",
       "         475046636  10368360710    203527   \n",
       "         377550424  15771663914  50015841   \n",
       "         530850018  22058239899  50024147   \n",
       "...                         ...       ...   \n",
       "50014815 413188001  16521677358  50012478   \n",
       "         641734831  22105131076  50014277   \n",
       "         268356658  36932456353  50010236   \n",
       "         196272909  10066997901  50009540   \n",
       "         23473499   38019470815  50010236   \n",
       "\n",
       "                                                             property  \\\n",
       "cat1     user_id                                                        \n",
       "28       532110457  21458:11399317;1628862:3251296;21475:137325;16...   \n",
       "         82830661                            21458:11580;21475:137325   \n",
       "         475046636  22724:40168;22729:40278;21458:21817;2770200:24...   \n",
       "         377550424  1628665:3233941;1628665:3233942;3914866:11580;...   \n",
       "         530850018  21458:205007542;43307470:5543413;2339128:62147...   \n",
       "...                                                               ...   \n",
       "50014815 413188001  21458:28155;5434803:3636603;2815901:22583732;1...   \n",
       "         641734831  21458:21906;13227811:51479;13230966:75369014;3...   \n",
       "         268356658  21458:10513072;12474507:706291650;3091143:9208...   \n",
       "         196272909  21458:21906;13229910:32056435;2191928:73664723...   \n",
       "         23473499   1628665:61550;1628665:3233940;1628665:3233936;...   \n",
       "\n",
       "                    buy_mount       day  \n",
       "cat1     user_id                         \n",
       "28       532110457          1  20131011  \n",
       "         82830661           1  20121101  \n",
       "         475046636          1  20121101  \n",
       "         377550424          1  20121123  \n",
       "         530850018          1  20140210  \n",
       "...                       ...       ...  \n",
       "50014815 413188001          1  20130107  \n",
       "         641734831          2  20141016  \n",
       "         268356658          1  20141027  \n",
       "         196272909          1  20141104  \n",
       "         23473499           1  20141104  \n",
       "\n",
       "[11797 rows x 5 columns]"
      ]
     },
     "execution_count": 465,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#第一层引用 引用第一层的两个index\n",
    "baby_trade.loc[[28,50014815]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 470,
   "id": "48e4dd14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>zs</th>\n",
       "      <th>ls</th>\n",
       "      <th>ww</th>\n",
       "      <th>zl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">python</th>\n",
       "      <th>期中</th>\n",
       "      <td>14</td>\n",
       "      <td>118</td>\n",
       "      <td>101</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>69</td>\n",
       "      <td>145</td>\n",
       "      <td>44</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">math</th>\n",
       "      <th>期中</th>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>128</td>\n",
       "      <td>87</td>\n",
       "      <td>102</td>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Chinese</th>\n",
       "      <th>期中</th>\n",
       "      <td>125</td>\n",
       "      <td>37</td>\n",
       "      <td>56</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>127</td>\n",
       "      <td>125</td>\n",
       "      <td>127</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             zs   ls   ww   zl\n",
       "python  期中   14  118  101  140\n",
       "        期末   69  145   44   18\n",
       "math    期中   82    3    2   31\n",
       "        期末  128   87  102  138\n",
       "Chinese 期中  125   37   56   74\n",
       "        期末  127  125  127   10"
      ]
     },
     "execution_count": 470,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 创建dataframe, 加入多层索引\n",
    "df3 = pd.DataFrame(np.random.randint(0,150,size=(6,4)),\n",
    "               columns = ['zs','ls','ww','zl'],\n",
    "               index = \n",
    "                [['python','python','math','math','Chinese','Chinese'],\n",
    "                 ['期中','期末','期中','期末','期中','期末']])\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24cd8a0b",
   "metadata": {},
   "source": [
    "class1=['python','python','math','math','Chinese','Chinese']\n",
    "class2=['期中','期末','期中','期末','期中','期末']\n",
    "m_index = pd.MultiIndex.from_arrays([class1,class2]) \n",
    "df3 = pd.DataFrame(np.random.randint(0,150,(6,4)),index=m_index) \n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 474,
   "id": "bed5ff2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">python</th>\n",
       "      <th>期中</th>\n",
       "      <td>97</td>\n",
       "      <td>91</td>\n",
       "      <td>59</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>13</td>\n",
       "      <td>74</td>\n",
       "      <td>34</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">math</th>\n",
       "      <th>期中</th>\n",
       "      <td>133</td>\n",
       "      <td>65</td>\n",
       "      <td>80</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>88</td>\n",
       "      <td>58</td>\n",
       "      <td>59</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Chinses</th>\n",
       "      <th>期中</th>\n",
       "      <td>115</td>\n",
       "      <td>26</td>\n",
       "      <td>138</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>60</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              0   1    2    3\n",
       "python  期中   97  91   59   69\n",
       "        期末   13  74   34   29\n",
       "math    期中  133  65   80   44\n",
       "        期末   88  58   59   62\n",
       "Chinses 期中  115  26  138  122\n",
       "        期末   60  11   14   16"
      ]
     },
     "execution_count": 474,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class1=['python','math','Chinses']\n",
    "class2=['期中','期末']\n",
    "m_index = pd.MultiIndex.from_product([class1,class2]) \n",
    "df3 = pd.DataFrame(np.random.randint(0,150,(6,4)),index=m_index) \n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 483,
   "id": "05e1a2c7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97"
      ]
     },
     "execution_count": 483,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.loc[('python','期中'),0]"
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "formats": "ipynb,Rmd"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
